SQL Serverからデータを取得するためにsqlcmdをインストールする
はじめに
データアナリティクス事業本部のkobayashiです。
最近AmazonLinux，macOS環境でSQL Serverからデータを取得する必要があったためどのような手段があるか調べていたところ「SQL Serverコマンドラインツール」なるものがあることを知り使ってみました。その導入方法と使い方を簡単にまとめます。
環境
- macOS Mojave
- SQL Server 2017
インストール方法
AmazonLinuxへのインストール
上記の公式サイトのリンク先の「RHEL 7 にツールをインストールする」の項に記載されている内容に従えば問題なくインストールできます。
$ curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo $ yum -y install mssql-tools
yum -y install mssql-toolsを実行すると途中で
The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746949 and found in /usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES', you indicate that you accept the license terms. Do you accept the license terms? (Enter YES or NO)
のようなライセンスに同意する必要があるので問題なければ「YES」をタイプすればインストールの完了です。
macOSへのインストール
開発者のmacOSでしたらHomebrewはインストールされていると思いますのでHomebrewを使ってインストールできます。
$ brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release $ brew update $ brew install mssql-tools
こちらも途中でライセンスへの同意確認がありますので問題なければ「YES」をタイプしてインストールの完了です。
コマンドラインツールの使い方
上記の手順でインストールが終わるとsqlcmd,bcpの2種類のコマンドが利用できるようになります。公式サイトの記述には
・sqlcmd:コマンド ライン ユーティリティ
・bcp:一括インポート エクスポート ユーティリティ
とあるようにsqlcmdはMySQLでいう所のmysqlコマンド、bcpはmysqldumpコマンド と考えれば良いです。
sqlcmdの使い方
SQL Serverなので「SQL Server認証」と「Windows認証」がありますがどちらの方法でも接続可能です。
# SQL Server認証 $ sqlcmd -S ホスト -U ユーザ -P パスワード # Windows認証 $ sqlcmd -S ホスト -E 1>
ポートがデフォルトの1433以外の場合は以下のようにホスト名の後に,(カンマ)でポート番号を指定すれば接続できます。
# SQL Server認証 $ sqlcmd -S ホスト,ポート番号 -U ユーザ -P パスワード # Windows認証 $ sqlcmd -S ホスト,ポート番号 -E 1>
ここまでの方法ですとSQL Serverに接続してSQLコマンドを入力して様々な操作が行えますが、SQLコマンドを直接実行して終了したい場合は-Qオプションで実行したいクエリを書けば実行可能です。
$ sqlcmd -S ホスト,ポート番号 -U ユーザ -P パスワード -Q "SELECT * FROM sys.objects;"
このオプションを利用すればクエリの実行実行結果をcsvファイルに出力することもできます。
$ sqlcmd -S ホスト,ポート番号 -U ユーザ -P パスワード -Q "SELECT * FROM sys.objects;" -W -s"," > sample.csv $ cat sample.csv name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published ----,---------,------------,---------,----------------,----,---------,-----------,-----------,-------------,------------,------------------- test1,3299048,NULL,1,0,U ,USER_TABLE,2019-11-08 14:34:05.487,2019-11-19 13:29:59.133,0,0,0 test2,4395065,NULL,1,0,U ,USER_TABLE,2019-12-09 13:52:24.507,2019-12-09 13:12:24.507,0,0,0
ここで追加のオプションを設定したのでその解説をします。
- -W : 列の後ろの空白を削除
- -s : 列の区切り文字を指定
ここで利用した以外にも様々なオプションが指定できるので詳しくは公式サイトをご確認ください。
bcpの使い方
bcpはバルクコピーの略でその名の通り一括エクスポート、インポートが行えます。
# エクスポート $ bcp DB名.スキーマ名.テーブル名 out test_export.txt -S ホスト(,ポート) -U ユーザ -P パスワード -c -t ',' # クエリを利用したエクスポート $ bcp "SELECT * FROM sys.objects;" queryout test_export.txt -S ホスト -U ユーザ -P パスワード -c -t ',' # インポート $ bcp DB名.スキーマ名.テーブル名 in test_export.txt -S ホスト -U ユーザ -P パスワード -c -t ','
sqlcmdでもファイルへのエクスポートは行なえますが単純なエクスポートしか行えません。一方、bcpを利用した場合には-fオプションでフォーマットファイルを利用すると囲み文字を付加するなど柔軟な操作が可能になります。
こちらも詳しくは公式サイトをご確認ください。
日本語名のテーブル操作時のエラー
bcpコマンドで日本語名のテーブルのエクスポートを行った場合にはターミナルの文字コードが原因でエラーが出てしまいます。
$ bcp test.schema.日本語名のテーブル out test_export.txt -S ホスト -U ユーザ -P パスワード -c -t ',' SQLState = 37000, NativeError = 102 ijˇLB?~Y[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'?' ??kdswe SQLState = 37000, NativeError = 8180 Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]??????Ȓ?wer?gM~[?gW_
この場合はqueryoutの方を使えばエラーは出ないのでそちらを使うことをおすすめします。
$ bcp "SELECT * FROM test.schema.日本語名のテーブル;" queryout test_export.txt -S ホスト -U ユーザ -P パスワード -c -t ','
まとめ
Linux、macOSでSQL Serverの操作を行えるコマンドラインツールのsqlcmdとデータの一括操作を行えるbcpコマンドのインストールから利用方法を紹介しました。 これらのコマンドを利用してシェルスクリプトを組めば簡単にデータのエクスポートやインポートを行えます。
最後まで読んで頂いてありがとうございました。
